using FreeSql;
using Microsoft.Extensions.Logging;
using SumerCoreDevOps.Domain.Entities;
using System.Transactions;

namespace SumerCoreDevOps.Infrastructure.Data;

/// <summary>
/// FreeSQL 数据库上下文提供者
/// </summary>
public class FreeSQLProvider : IDisposable
{
    private readonly IFreeSql _freeSql;
    private readonly ILogger<FreeSQLProvider> _logger;

    public IFreeSql Orm => _freeSql;

    public FreeSQLProvider(string connectionString, ILogger<FreeSQLProvider> logger)
    {
        _logger = logger;

        _freeSql = new FreeSqlBuilder()
            .UseConnectionString(DataType.MySql, connectionString)
            .UseAutoSyncStructure(false) // 使用手动迁移
            .UseNoneCommandParameter(true)
            .UseMonitorCommand(cmd =>
            {
                _logger.LogDebug("SQL: {Sql}", cmd.CommandText);
            })
            .Build();

        // 配置实体类型
        ConfigureEntities();
    }

    /// <summary>
    /// 配置实体类型
    /// </summary>
    private void ConfigureEntities()
    {
        // 实体配置 - 默认值已在实体类中通过属性初始化设置
    }

    /// <summary>
    /// 执行数据库迁移（向上）
    /// </summary>
    public void MigrateUp()
    {
        try
        {
            _logger.LogInformation("开始执行数据库迁移...");

            // 同步表结构 - 确保所有实体都被包含
            _freeSql.CodeFirst.SyncStructure(
                // Workflow related
                typeof(WorkflowDefinition),
                typeof(Stage),
                typeof(Step),
                typeof(Pipeline),
                typeof(PipelineInstance),
                typeof(TaskEntity),
                typeof(TaskExecutionLog),

                // Node related
                typeof(NodeStatus),
                typeof(NodeJoinToken),
                typeof(NodeOperationLog),
                typeof(MasterNode),

                // Build & Docker related
                typeof(BuildHistory),
                typeof(BuildArtifact),
                typeof(DockerRegistry),
                typeof(ImageTagRule),

                // Git related
                typeof(GitTriggerRule),

                // Identity & Access related
                typeof(User),
                typeof(Role),
                typeof(Permission),
                typeof(UserRole),
                typeof(RolePermission),
                typeof(UserOperationLog),
                typeof(TokenBlacklist),

                // System Configuration
                typeof(SystemConfig)
            );

            _logger.LogInformation("数据库迁移完成");
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "数据库迁移失败");
            throw;
        }
    }

    /// <summary>
    /// 回滚数据库迁移（向下）
    /// </summary>
    public void MigrateDown()
    {
        try
        {
            _logger.LogInformation("开始回滚数据库迁移...");

            // 按依赖关系逆序删除表
            var tables = new[]
            {
                // 关联表
                "user_roles",
                "role_permissions",

                // 日志表和黑名单
                "user_operation_logs",
                "node_operation_logs",
                "task_execution_logs",
                "token_blacklist",

                // 任务和构建相关
                "tasks",
                "pipeline_instances",
                "pipelines",
                "build_histories",
                "build_artifacts",

                // 工作流相关
                "steps",
                "stages",
                "workflow_definitions",

                // 规则配置
                "git_trigger_rules",
                "image_tag_rules",

                // 节点相关
                "node_join_tokens",
                "node_status",
                "master_nodes",

                // Docker相关
                "docker_registries",

                // 系统配置
                "system_configs",

                // 权限系统
                "permissions",
                "roles",
                "users"
            };

            foreach (var table in tables)
            {
                _freeSql.Ado.ExecuteNonQuery($"DROP TABLE IF EXISTS `{table}`");
                _logger.LogInformation("已删除表: {Table}", table);
            }

            _logger.LogInformation("数据库迁移回滚完成");
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "数据库迁移回滚失败");
            throw;
        }
    }

    /// <summary>
    /// 检查数据库连接
    /// </summary>
    public async Task<bool> CheckConnectionAsync()
    {
        try
        {
            await _freeSql.Ado.ExecuteNonQueryAsync("SELECT 1");
            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "数据库连接检查失败");
            return false;
        }
    }

    /// <summary>
    /// 获取数据库版本信息
    /// </summary>
    public async Task<string> GetDatabaseVersionAsync()
    {
        try
        {
            var version = await _freeSql.Ado.QuerySingleAsync<string>("SELECT VERSION()");
            return version ?? "Unknown";
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取数据库版本失败");
            return "Error";
        }
    }

    /// <summary>
    /// 在事务中执行操作（同步版本）
    /// </summary>
    /// <typeparam name="T">返回值类型</typeparam>
    /// <param name="action">要执行的操作</param>
    /// <param name="isolationLevel">事务隔离级别，默认可重复读</param>
    /// <returns>操作的返回结果</returns>
    /// <exception cref="Exception">当事务执行失败时抛出</exception>
    public T ExecuteInTransaction<T>(Func<IFreeSql, T> action, IsolationLevel isolationLevel = IsolationLevel.RepeatableRead)
    {
        try
        {
            // 使用TransactionScope来实现事务
            using var scope = new TransactionScope(TransactionScopeOption.Required, 
                new TransactionOptions { IsolationLevel = isolationLevel },
                TransactionScopeAsyncFlowOption.Suppress);
                
            var result = action(_freeSql);
            
            scope.Complete();
            _logger.LogDebug("数据库事务已提交");
            
            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "数据库事务执行失败，已回滚");
            throw;
        }
    }

    /// <summary>
    /// 在事务中执行操作（不带返回值的同步版本）
    /// </summary>
    /// <param name="action">要执行的操作</param>
    /// <param name="isolationLevel">事务隔离级别，默认可重复读</param>
    public void ExecuteInTransaction(Action<IFreeSql> action, IsolationLevel isolationLevel = IsolationLevel.RepeatableRead)
    {
        ExecuteInTransaction<object>(orm =>
        {
            action(orm);
            return null;
        }, isolationLevel);
    }

    /// <summary>
    /// 在事务中执行异步操作
    /// </summary>
    /// <typeparam name="T">返回值类型</typeparam>
    /// <param name="action">要执行的异步操作</param>
    /// <param name="isolationLevel">事务隔离级别，默认可重复读</param>
    /// <returns>操作的返回结果</returns>
    /// <exception cref="Exception">当事务执行失败时抛出</exception>
    public async Task<T> ExecuteInTransactionAsync<T>(Func<IFreeSql, Task<T>> action, IsolationLevel isolationLevel = IsolationLevel.RepeatableRead)
    {
        try
        {
            // 使用TransactionScope来实现异步事务
            using var scope = new TransactionScope(TransactionScopeOption.Required, 
                new TransactionOptions { IsolationLevel = isolationLevel },
                TransactionScopeAsyncFlowOption.Enabled);
                
            var result = await action(_freeSql);
            
            scope.Complete();
            _logger.LogDebug("数据库事务已提交");
            
            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "数据库事务执行失败，已回滚");
            throw;
        }
    }

    /// <summary>
    /// 在事务中执行异步操作（不带返回值的异步版本）
    /// </summary>
    /// <param name="action">要执行的异步操作</param>
    /// <param name="isolationLevel">事务隔离级别，默认可重复读</param>
    public Task ExecuteInTransactionAsync(Func<IFreeSql, Task> action, IsolationLevel isolationLevel = IsolationLevel.RepeatableRead)
    {
        return ExecuteInTransactionAsync<object>(async orm =>
        {
            await action(orm);
            return null;
        }, isolationLevel);
    }

    public void Dispose()
    {
        _freeSql?.Dispose();
    }
}
